package com.clippick.database;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

@SuppressWarnings({ "static-access" })
public class HistoryDBAdapter {
	// common
	//private Context mContext;

	private static final String DATABASE_NAME = "HistoryFeeds.db";
	private static final String DATABASE_TABLE_PRE = "HistoryFeedsTB_";
	private static final String SQL_CREATE_TEMP_TABLE = "CREATE TABLE IF NOT EXISTS temp_table (id INTEGER PRIMARY KEY AUTOINCREMENT, NO TEXT, B_KEEP TEXT)";
	private static final String TEMP_TABLE = "temp_table";
	private static String DATABASE_TABLE = "";
	private static final int DATABASE_VERSION = 1;

	// database key
	public final static String KEY_DB_NO = "NO";
	public final static String KEY_DB_DEVICE = "HISTORY_DEVICE";
	public final static String KEY_DB_DATE = "HISTORY_DATE";
	public final static String KEY_DB_KEEPBOX = "B_KEEP";
	public final static String KEY_DB_TEXT = "HISTORY_TEXT";
	public final static String KEY_DB_CONTENTS = "HISTORY_CONTENTS";
	public final static String KEY_DB_IMG = "HISTORY_IMG";

	private String DATABASE_TABLE_CREATE;
	private String DATABASE_TABLE_DROP;

	private SQLiteDatabase mDb;
	private DatabaseHelper mDbHelper;

	public HistoryDBAdapter(Context context, String basicTableName) {
		//mContext = context;

		createTable(basicTableName);
		mDbHelper = new DatabaseHelper(context, DATABASE_NAME, null,
				DATABASE_VERSION);
	}

	/**
	public HistoryDBAdapter open() throws SQLException {
		mDb = mDbHelper.getWritableDatabase();
		mDbHelper.creteTable(mDb);
		return this;
	}
	*/

	public void open() throws SQLException{
		mDb=mDbHelper.getWritableDatabase();
	}
	
	public boolean isOpenDBfile() {
		return mDb!=null? mDb.isOpen() : false;
	}

	public void close() {
		mDb.close();
	}

	// 테이블 만들기 NO, HISTORY_DEVICE, HISTORY_DATE, HISTORY_CONTENTS, HISTORY_TEXT,
	// HISTORY_IMG, B_KEEP
	public void createTable(String databaseID) {
		DATABASE_TABLE = DATABASE_TABLE_PRE + databaseID;

		DATABASE_TABLE_CREATE = "CREATE TABLE IF NOT EXISTS " + DATABASE_TABLE
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT,"
				+ "NO  								TEXT NOT NULL,"
				+ "B_KEEP    						TEXT NOT NULL,"
				+ "HISTORY_DEVICE    				TEXT NOT NULL,"
				+ "HISTORY_DATE						TEXT NOT NULL,"
				+ "HISTORY_TEXT						TEXT NOT NULL,"
				+ "HISTORY_CONTENTS 				TEXT NOT NULL,"
				+ "HISTORY_IMG						TEXT NOT NULL" + ");";

		DATABASE_TABLE_DROP = "DROP TABLE IF EXISTS " + DATABASE_TABLE;
	}

	// 피드 DB에 저장하기
	public long insertFeed(Map<String, String> feed) {
		long idx = Integer.parseInt(feed.get(this.KEY_DB_NO));
		//boolean isKeepBox = false;
		if (feed.get(this.KEY_DB_KEEPBOX).equals("1")) {
			//isKeepBox = true;
		}

		Cursor findUpPos = getFeedByIndex(idx);

		if (findUpPos.getCount() != 0) {
			findUpPos.close();
			return -1;
		}
		findUpPos.close();

		ContentValues values = new ContentValues();
		values.put(KEY_DB_CONTENTS, feed.get(KEY_DB_CONTENTS));
		values.put(KEY_DB_KEEPBOX, feed.get(KEY_DB_KEEPBOX));
		values.put(KEY_DB_DEVICE, feed.get(KEY_DB_DEVICE));
		values.put(KEY_DB_DATE, feed.get(KEY_DB_DATE));
		values.put(KEY_DB_TEXT, feed.get(KEY_DB_TEXT));
		values.put(KEY_DB_IMG, feed.get(KEY_DB_IMG));
		values.put(KEY_DB_NO, feed.get(KEY_DB_NO));
		return mDb.insert(DATABASE_TABLE, null, values);
	}

	// 피드 DB 업데이트하기
	public boolean updateFeed(Map<String, String> feed) {
		long idx = Integer.parseInt(feed.get(KEY_DB_NO).toString());
		// Cursor c = getFeedByIndex(idx);

		// long rowID = -1;
		// if (c.moveToFirst()) {
		// rowID = c.getLong(c.getColumnIndexOrThrow("id"));
		// }
		// c.close();

		ContentValues values = new ContentValues();
		values.put(KEY_DB_CONTENTS, feed.get(KEY_DB_CONTENTS));
		values.put(KEY_DB_KEEPBOX, feed.get(KEY_DB_KEEPBOX));
		values.put(KEY_DB_DEVICE, feed.get(KEY_DB_DEVICE));
		values.put(KEY_DB_DATE, feed.get(KEY_DB_DATE));
		values.put(KEY_DB_TEXT, feed.get(KEY_DB_TEXT));
		values.put(KEY_DB_IMG, feed.get(KEY_DB_IMG));
		values.put(KEY_DB_NO, feed.get(KEY_DB_NO));
		return mDb.update(DATABASE_TABLE, values, "NO" + "=" + idx, null) > 0;
	}

	/**
	// 피드 삭제하기
	public boolean deleteFeedAll() {
		return mDb.delete(DATABASE_TABLE, null, null) > 0;
		// checked - no used
	}
	*/

	public boolean deleteFeed(long index) {
		Cursor findDelPos = getFeedByIndex(index);

		if (findDelPos.getCount() == 0) {
			findDelPos.close();
			return false;
		}
		findDelPos.close();

		try {
			mDb.execSQL("DELETE FROM " + DATABASE_TABLE + " WHERE " + KEY_DB_NO
					+ "=" + "'" + index + "'");
			return true;
		} catch (Exception e) {
			return false;
		}
		// checked
	}

	// NO 값으로 저장된 피드찾기
	public Cursor getFeedByIndex(long index) throws SQLException {
		Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {
				KEY_DB_CONTENTS, KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE,
				KEY_DB_TEXT, KEY_DB_IMG, KEY_DB_NO }, KEY_DB_NO + "=" + "'"
				+ index + "'", null, null, null, null, null);

		if (mCursor != null)
			mCursor.moveToFirst();
		return mCursor;
		// checked
	}

	/**
	// 피드 갯 수 가져오기
	public long getFeedCount() throws SQLException {
		Cursor mCursor = fetchFeedAll();
		long count = 0;
		if (mCursor != null) {
			count = mCursor.getCount();
		}
		mCursor.close();
		return count;
		
		// checked - no used
	}
	*/

	public List<String> getAllText() {
		List<String> result = new ArrayList<String>();
		Cursor c = fetchFeedAll();
		if (c != null) {
			if (c.moveToFirst()) {
				do {
					String text = c
							.getString(c
									.getColumnIndexOrThrow(HistoryDBAdapter.KEY_DB_TEXT));
					result.add(text);
				} while (c.moveToNext());
			}
		}
		return result;
		// checked
	}

	// Cursor
	// 전체 피드 가져오기
	public Cursor fetchFeedAll() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_DB_CONTENTS,
				KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE, KEY_DB_TEXT,
				KEY_DB_IMG, KEY_DB_NO }, null, null, null, null, null, null);
		// checked - locally used
	}

	// 날짜 역순으로 피드 가져오기 1
	public Cursor fetchFeedAllOrderByDescDate() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_DB_CONTENTS,
				KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE, KEY_DB_TEXT,
				KEY_DB_IMG, KEY_DB_NO }, null, null, null, null, KEY_DB_DATE
				+ " desc", null);
		// checked 
	}

	/**
	// 날짜 순으로 피드 가져오기 2
	public Cursor fetchFeedAllOrderByAscDate() {
		return mDb.query(DATABASE_TABLE, new String[] { KEY_DB_CONTENTS,
				KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE, KEY_DB_TEXT,
				KEY_DB_IMG, KEY_DB_NO }, null, null, null, null, KEY_DB_DATE
				+ " asc", null);
		// checked - no used
	}
	*/

	// 날짜 역순으로 별피드 가져오기 1
	public Cursor fetchStarFeedOrderByDescDate(String _isKeepBox)
			throws SQLException {
		Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {
				KEY_DB_CONTENTS, KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE,
				KEY_DB_TEXT, KEY_DB_IMG, KEY_DB_NO }, KEY_DB_KEEPBOX + "="
				+ "'" + _isKeepBox + "'", null, null, null, KEY_DB_DATE
				+ " desc", null);

		if (mCursor != null)
			mCursor.moveToFirst();
		return mCursor;
		// checked 
	}

	/**
	// 날짜 순으로 별피드 가져오기 2
	public Cursor fetchStarFeedOrderByAscDate(String _isKeepBox)
			throws SQLException {
		Cursor mCursor = mDb.query(true, DATABASE_TABLE, new String[] {
				KEY_DB_CONTENTS, KEY_DB_KEEPBOX, KEY_DB_DEVICE, KEY_DB_DATE,
				KEY_DB_TEXT, KEY_DB_IMG, KEY_DB_NO }, KEY_DB_KEEPBOX + "="
				+ "'" + _isKeepBox + "'", null, null, null, KEY_DB_DATE
				+ " asc", null);

		if (mCursor != null)
			mCursor.moveToFirst();
		return mCursor;
		// checked - no used
	}
	*/

	public Cursor findRemovedFeeds() {
		Cursor mCursor = mDb.rawQuery("SELECT NO FROM " + DATABASE_TABLE
				+ " WHERE NO NOT IN (SELECT NO FROM temp_table)", null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		return mCursor;
		// checked
	}

	public Cursor findNewFeeds() {
		Cursor mCursor = mDb.rawQuery(
				"SELECT NO FROM temp_table WHERE NO NOT IN (SELECT NO FROM "
						+ DATABASE_TABLE + ")", null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		return mCursor;
		// checked
	}

	public Cursor findChangedFeeds() {
		Cursor mCursor = mDb
				.rawQuery(
						"SELECT FT.NO FROM "
								+ DATABASE_TABLE
								+ " FT JOIN temp_table TT ON FT.NO = TT.NO WHERE NOT(FT.B_KEEP=TT.B_KEEP)",
						null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}

		return mCursor;
		// checked
	}

	public boolean deleteTempFeedAll() {
		return mDb.delete("temp_table", null, null) > 0;
		// checked
	}

	public long insertFeedIntoTempTable(Map<String, String> feed) {
		ContentValues values = new ContentValues();
		values.put(KEY_DB_KEEPBOX, feed.get(KEY_DB_KEEPBOX));
		values.put(KEY_DB_NO, feed.get(KEY_DB_NO));
		return mDb.insert(TEMP_TABLE, null, values);
		// checked
	}

	// DB 생성하기
	private class DatabaseHelper extends SQLiteOpenHelper {
		public DatabaseHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase mDb) {
			mDb.execSQL(DATABASE_TABLE_CREATE);
			mDb.execSQL(SQL_CREATE_TEMP_TABLE);
		}

		/**
		public void creteTable(SQLiteDatabase mDb) {
			onCreate(mDb);
		}
		*/

		@Override
		public void onUpgrade(SQLiteDatabase mDb, int oldVersion, int newVersion) {
			Log.w("MY_TAG", "Upgrading DB from version" + oldVersion + " to"
					+ newVersion + ", which will destroy all old data");

			mDb.execSQL(DATABASE_TABLE_DROP);
			onCreate(mDb);
		}
	}
}
